package com.serviceprovider.business.module.admin.user.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.user.model.OldUser;
import com.serviceprovider.business.module.admin.user.model.UserProfile;
import com.serviceprovider.business.module.admin.user.services.dao.interfaces.IProfileService;
import com.serviceprovider.business.module.admin.user.valueobjects.UserSearchResultVO;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransStatusDao;
import com.serviceprovider.business.module.user.model.impl.UserSearchCriteria;
import com.serviceprovider.business.utility.maintenance.settings.AppSettings;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

//~--- JDK imports ------------------------------------------------------------

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * * THIS CLASS IS NO LONGER IN USE This class is an implementation of
 * ProfileService
 *
 * @author mrduongnv@gmail.com
 *
 */
@Service("profileService")
public class ProfileServiceDaoImpl extends JdbcDaoSupport implements IProfileService {
    private static final String F_BUSINESS_ADDRESS      = "business_address";
    private static final String F_BUSINESS_CITY         = "business_city";
    private static final String F_BUSINESS_COUNTRY      = "business_country";
    private static final String F_BUSINESS_COUNTY       = "business_county";
    private static final String F_BUSINESS_FAX          = "business_fax";
    private static final String F_BUSINESS_NAME         = "businessname";
    private static final String F_BUSINESS_PHONE        = "business_phone";
    private static final String F_BUSINESS_STATE        = "business_state";
    private static final String F_BUSINESS_ZIP          = "business_zip";
    private static final String F_CONTACT_ADDRESS       = "contact_address";
    private static final String F_CONTACT_CELL          = "contact_cell";
    private static final String F_CONTACT_CELL_PROVIDER = "contact_cell_provider";
    private static final String F_CONTACT_CITY          = "contact_city";
    private static final String F_CONTACT_COUNTRY       = "contact_country";
    private static final String F_CONTACT_COUNTY        = "contact_county";
    private static final String F_CONTACT_FAX           = "contact_fax";
    private static final String F_CONTACT_PHONE         = "contact_phone";
    private static final String F_CONTACT_PHONE_2       = "contact_phone_2";
    private static final String F_CONTACT_STATE         = "contact_state";
    private static final String F_CONTACT_ZIP           = "contact_zip";
    private static final String F_EFIN                  = "efin";
    private static final String F_FIRST_NAME            = "firstname";
    private static final String F_ID                    = "id";
    private static final String F_LAST_NAME             = "lastname";
    private static final String F_MIDDLE_NAME           = "middlename";
    private static final String F_NOTIFY_ALERT          = "pref_message_notify_alert";
    private static final String F_NOTIFY_TYPE           = "pref_message_notify_type";
    private static final String F_PAGER                 = "pager";
    private static final String F_SHIPPING_ADDRESS      = "shipping_address";
    private static final String F_SHIPPING_CITY         = "shipping_city";
    private static final String F_SHIPPING_COUNTRY      = "shipping_country";
    private static final String F_SHIPPING_COUNTY       = "shipping_county";
    private static final String F_SHIPPING_FAX          = "shipping_fax";
    private static final String F_SHIPPING_PHONE        = "shipping_phone";
    private static final String F_SHIPPING_STATE        = "shipping_state";
    private static final String F_SHIPPING_ZIP          = "shipping_zip";
    private static final String F_SSN                   = "ssn";
    private static final String F_USER_ID               = "user_id";
    private static final String INSERT_A_PROFILE        = new StringBuilder("insert into adm_user_profiles (").append(
                                                       F_FIRST_NAME).append(",").append(F_MIDDLE_NAME).append(
                                                       ",").append(F_LAST_NAME).append(",").append(F_EFIN).append(
                                                       ",").append(F_SSN).append(",").append(F_BUSINESS_NAME).append(
                                                       ",").append(F_PAGER).append(",").append(
                                                       F_CONTACT_ADDRESS).append(",").append(F_CONTACT_STATE).append(
                                                       ",").append(F_CONTACT_ZIP).append(",").append(
                                                       F_CONTACT_FAX).append(",").append(F_CONTACT_COUNTRY).append(
                                                       ",").append(F_CONTACT_COUNTY).append(",").append(
                                                       F_CONTACT_CITY).append(",").append(F_CONTACT_PHONE).append(
                                                       ",").append(F_CONTACT_PHONE_2).append(",").append(
                                                       F_CONTACT_CELL).append(",").append(
                                                       F_CONTACT_CELL_PROVIDER).append(",").append(
                                                       F_BUSINESS_ADDRESS).append(",").append(F_BUSINESS_STATE).append(
                                                       ",").append(F_BUSINESS_ZIP).append(",").append(
                                                       F_BUSINESS_FAX).append(",").append(F_BUSINESS_COUNTRY).append(
                                                       ",").append(F_BUSINESS_COUNTY).append(",").append(
                                                       F_BUSINESS_CITY).append(",").append(F_BUSINESS_PHONE).append(
                                                       ",").append(F_SHIPPING_ADDRESS).append(",").append(
                                                       F_SHIPPING_STATE).append(",").append(F_SHIPPING_ZIP).append(
                                                       ",").append(F_SHIPPING_FAX).append(",").append(
                                                       F_SHIPPING_COUNTY).append(",").append(F_SHIPPING_COUNTRY).append(
                                                       ",").append(F_SHIPPING_CITY).append(",").append(
                                                       F_SHIPPING_PHONE).append(",").append(F_NOTIFY_TYPE).append(
                                                       ",").append(F_USER_ID).append(",").append(F_NOTIFY_ALERT).append(
                                                       ") values (?,?,?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,"
                                                       + "?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,"
                                                       + "?,?,?,?,?,?,?)").toString();
    private static final String UPDATE_A_PROFILE = new StringBuilder("update adm_user_profiles set ").append(
                                                       F_FIRST_NAME).append(" = ?, ").append(F_MIDDLE_NAME).append(
                                                       " = ?, ").append(F_LAST_NAME).append(" = ?, ").append(
                                                       F_EFIN).append(" = ?, ").append(F_SSN).append(" = ?, ").append(
                                                       F_BUSINESS_NAME).append(" = ?, ").append(F_PAGER).append(
                                                       " = ?, ").append(F_CONTACT_ADDRESS).append(" = ?, ").append(
                                                       F_CONTACT_STATE).append(" = ?, ").append(F_CONTACT_ZIP).append(
                                                       " = ?, ").append(F_CONTACT_FAX).append(" = ?, ").append(
                                                       F_CONTACT_COUNTRY).append(" = ?, ").append(
                                                       F_CONTACT_COUNTY).append(" = ?, ").append(F_CONTACT_CITY).append(
                                                       " = ?, ").append(F_CONTACT_PHONE).append(" = ?, ").append(
                                                       F_CONTACT_PHONE_2).append(" = ?, ").append(
                                                       F_CONTACT_CELL).append(" = ?, ").append(
                                                       F_CONTACT_CELL_PROVIDER).append(" = ?, ").append(
                                                       F_BUSINESS_ADDRESS).append(" = ?, ").append(
                                                       F_BUSINESS_STATE).append(" = ?, ").append(F_BUSINESS_ZIP).append(
                                                       " = ?, ").append(F_BUSINESS_FAX).append(" = ?, ").append(
                                                       F_BUSINESS_COUNTRY).append(" = ?, ").append(
                                                       F_BUSINESS_COUNTY).append(" = ?, ").append(
                                                       F_BUSINESS_CITY).append(" = ?, ").append(
                                                       F_BUSINESS_PHONE).append(" = ?, ").append(
                                                       F_SHIPPING_ADDRESS).append(" = ?, ").append(
                                                       F_SHIPPING_STATE).append(" = ?,").append(F_SHIPPING_ZIP).append(
                                                       " = ?, ").append(F_SHIPPING_FAX).append(" = ?, ").append(
                                                       F_SHIPPING_COUNTRY).append(" = ?, ").append(
                                                       F_SHIPPING_COUNTY).append(" = ?, ").append(
                                                       F_SHIPPING_CITY).append(" = ?, ").append(
                                                       F_SHIPPING_PHONE).append(" = ?, ").append(F_NOTIFY_TYPE).append(
                                                       " = ?, ").append(F_USER_ID).append(" = ?, ").append(
                                                       F_NOTIFY_ALERT).append(" = ? ").append("where ").append(
                                                       F_ID).append(" = ?").toString();
    private static final String SELECT_PROFILE_BY_USERID =
        new StringBuilder("select * from adm_user_profiles where ").append(F_USER_ID).append(" = ?").toString();
    private static final String SELECT_A_PROFILE =
        new StringBuilder("select * from adm_user_profiles where ").append(F_ID).append(" = ?").toString();
    private static final String SEARCH_PROFILE =
        new StringBuffer(
            "select pro.*, usr.id as userid, usr.email as useremail from adm_user_profiles pro, "
            + " adm_users usr,role rol where pro.").append(F_USER_ID).append(" = usr.id").append(
                " and usr.role=rol.id and rol.id = ").toString();
    private static final String GET_TRANSACTIONS_BY_STATUS =
        new StringBuffer("select count(transaction_id) from sp_transaction_details tr").append(
            ", adm_users usr where tr.user_id=usr.id ").append("and usr.id=? ").append(
            "and tr.transaction_status=?").toString();
    private static final String GET_ORDER_STATUS     = new StringBuffer("select id from order_status").toString();
    private static final String GET_ORDERS_BY_STATUS =
        new StringBuffer("select count(order_id) from sp_order_details ord, adm_users usr").append(
            " where ord.user_id=usr.id and usr.id=?").append(" and ord.order_status_id=?").toString();
    private static final String GET_ALL_TRANSACTIONS =
        new StringBuffer("select count(transaction_id) from sp_transaction_details tr").append(
            ", adm_users usr where tr.user_id=usr.id ").append("and usr.id=? ").toString();
    private static final String DELETE_PROFILE_BY_USERID =
        new StringBuilder("delete from adm_user_profiles where ").append(F_USER_ID).append(" = ?").toString();
    private static final String DELETE_A_PROFILE =
        new StringBuilder("delete from adm_user_profiles where ").append(F_ID).append(" = ?").toString();
    @Autowired
    private ITransStatusDao transactionStatusDao;

    @Autowired
    public ProfileServiceDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    public void save(UserProfile profile, int userId) {
        String sql = (profile.getId() == null)
                     ? INSERT_A_PROFILE
                     : UPDATE_A_PROFILE;

        getJdbcTemplate().update(sql, getParams(profile, userId));
    }

    public void update(UserProfile profile) {
        System.out.println(">>>>>>>>>>>profile" + profile);
        System.out.println("profile id" + profile.getId());

        String   sql          = UPDATE_A_PROFILE;
        Object[] insertParams = fillParams(profile, profile.getUserId());
        Object[] updateParams = new Object[insertParams.length + 1];

        for (int i = 0; i < updateParams.length - 1; i++) {
            updateParams[i] = insertParams[i];
        }

        updateParams[updateParams.length - 1] = profile.getId();
        getJdbcTemplate().update(sql, updateParams);
    }

    private Object[] getParams(UserProfile profile, int userId) {
        Object[] insertParams = fillParams(profile, userId);

        if (profile.getId() == null) {
            return insertParams;
        } else {
            Object[] updateParams = new Object[insertParams.length + 1];

            for (int i = 0; i < updateParams.length - 1; i++) {
                updateParams[i] = insertParams[i];
            }

            updateParams[updateParams.length - 1] = profile.getId();

            return updateParams;
        }
    }

    private Object[] fillParams(UserProfile profile, int userId) {
        return new Object[] {
            profile.getFirstName(), profile.getMiddleName(), profile.getLastName(), profile.getEfin(), profile.getSsn(),
            profile.getBusinessName(), profile.getPager(), profile.getContactAddress(), profile.getContactState(),
            profile.getContactZip(), profile.getContactFax(), profile.getContactCountry(), profile.getContactCounty(),
            profile.getContactCity(), profile.getFirstContactPhone(), profile.getSecondContactPhone(),
            profile.getContactCell(), profile.getContactCellProvider(), profile.getBusinessAddress(),
            profile.getBusinessState(), profile.getBusinessZip(), profile.getBusinessFax(),
            profile.getBusinessCountry(), profile.getBusinessCounty(), profile.getBusinessCity(),
            profile.getBusinessPhone(), profile.getShippingAddress(), profile.getShippingState(),
            profile.getShippingZip(), profile.getShippingFax(), profile.getShippingCountry(),
            profile.getShippingCounty(), profile.getShippingCity(), profile.getShippingPhone(),
            profile.getPrefMessageNotifyType(), userId, profile.getPrefMessageNotifyAlert()
        };
    }

    public void removeByUserId(int userId) {
        getJdbcTemplate().update(DELETE_PROFILE_BY_USERID, new Object[] { userId });
    }

    public void remove(int id) {
        getJdbcTemplate().update(DELETE_A_PROFILE, new Object[] { id });
    }

    private UserProfile getProfile(String query, Object[] params) {
        RowMapper mapper = new RowMapper() {
            public UserProfile mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserProfile profile = new UserProfile();

                profile.setId(rs.getInt(F_ID));
                profile.setFirstName(rs.getString(F_FIRST_NAME));
                profile.setMiddleName(rs.getString(F_MIDDLE_NAME));
                profile.setLastName(rs.getString(F_LAST_NAME));
                profile.setEfin(rs.getString(F_EFIN));
                profile.setSsn(rs.getString(F_SSN));
                profile.setBusinessName(rs.getString(F_BUSINESS_NAME));
                profile.setPager(rs.getString(F_PAGER));
                profile.setContactAddress(rs.getString(F_CONTACT_ADDRESS));
                profile.setContactState(rs.getString(F_CONTACT_STATE));
                profile.setContactZip(rs.getString(F_CONTACT_ZIP));
                profile.setContactFax(rs.getString(F_CONTACT_FAX));
                profile.setContactCountry(rs.getString(F_CONTACT_COUNTRY));
                profile.setContactCity(rs.getString(F_CONTACT_COUNTY));
                profile.setContactCounty(rs.getString(F_CONTACT_CITY));
                profile.setFirstContactPhone(rs.getString(F_CONTACT_PHONE));
                profile.setSecondContactPhone(rs.getString(F_CONTACT_PHONE));
                profile.setContactCell(rs.getString(F_CONTACT_CELL));
                profile.setContactCellProvider(rs.getString(F_CONTACT_CELL_PROVIDER));
                profile.setBusinessAddress(rs.getString(F_BUSINESS_ADDRESS));
                profile.setBusinessState(rs.getString(F_BUSINESS_STATE));
                profile.setBusinessZip(rs.getString(F_BUSINESS_ZIP));
                profile.setBusinessFax(rs.getString(F_BUSINESS_FAX));
                profile.setBusinessCountry(rs.getString(F_BUSINESS_COUNTRY));
                profile.setBusinessCity(rs.getString(F_BUSINESS_CITY));
                profile.setBusinessCounty(rs.getString(F_BUSINESS_COUNTY));
                profile.setBusinessPhone(rs.getString(F_BUSINESS_PHONE));
                profile.setShippingAddress(rs.getString(F_SHIPPING_ADDRESS));
                profile.setShippingState(rs.getString(F_SHIPPING_STATE));
                profile.setShippingZip(rs.getString(F_SHIPPING_ZIP));
                profile.setShippingFax(rs.getString(F_SHIPPING_FAX));
                profile.setShippingCountry(rs.getString(F_SHIPPING_COUNTRY));
                profile.setShippingCity(rs.getString(F_SHIPPING_COUNTY));
                profile.setShippingCounty(rs.getString(F_SHIPPING_CITY));
                profile.setShippingPhone(rs.getString(F_SHIPPING_PHONE));
                profile.setPrefMessageNotifyType(rs.getInt(F_NOTIFY_TYPE));
                profile.setPrefMessageNotifyAlert(rs.getInt(F_NOTIFY_ALERT));
                profile.setUserId(rs.getInt(F_USER_ID));

                return profile;
            }
        };

        try {
            return (UserProfile) getJdbcTemplate().queryForObject(query, params, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public UserProfile getById(int id) {
        return getProfile(SELECT_A_PROFILE, new Object[] { id });
    }

    public UserProfile getByUser(int userId) {
        return getProfile(SELECT_PROFILE_BY_USERID, new Object[] { userId });
    }

    public List<UserSearchResultVO> getBySearchCriteria(UserSearchCriteria criteria) {
        String query = SEARCH_PROFILE;

        query += criteria.getRoleId();

        if ((criteria.getBusinessName() != null) &&!criteria.getBusinessName().equals("")) {
            query += " and pro." + F_BUSINESS_NAME;
            query += " like '%" + criteria.getBusinessName() + "%'";
        }

//      if (criteria.getCountry() != null
//                      && !criteria.getCountry().equals("")) {
//              query += " and pro." + F_CONTACT_COUNTRY; 
//              query += " like '%" + criteria.getCountry() + "%'";
//      }
//      
//      if (criteria.getCity() != null
//                      && !criteria.getCity().equals("")) {
//              query += " and pro." + F_CONTACT_CITY; 
//              query += " like '%" + criteria.getCity() + "%'";
//      }
//      
//      if (criteria.getZip() != null
//                      && !criteria.getZip().equals("")) {
//              query += " and pro." + F_CONTACT_ZIP; 
//              query += " like '%" + criteria.getZip() + "%'";
//      }               
//
//      if (criteria.getEfin() != null
//                      && !criteria.getEfin().equals("")) {
//              query += " and pro." + F_EFIN; 
//              query += " like '%" + criteria.getEfin() + "%'";
//      }
        if ((criteria.getEmail() != null) &&!criteria.getEmail().equals("")) {
            query += " and usr.email";
            query += " like '%" + criteria.getEmail() + "%'";
        }

        System.out.println("Role Id = " + criteria.getRoleId());
        System.out.println("SQL = " + query + ";");

        List                     list       = getJdbcTemplate().queryForList(query);
        List<UserSearchResultVO> listResult = new ArrayList<UserSearchResultVO>();
        Map                      row        = null;
        UserProfile              profile    = null;
        UserSearchResultVO       vo         = null;
        OldUser                  user       = null;

        // Gets order statuses
        List          listOrdSt         = getJdbcTemplate().queryForList(GET_ORDER_STATUS);
        List<Integer> listOrderStatuses = new ArrayList<Integer>();

        for (Object ord : listOrdSt) {
            Map m        = (Map) ord;
            int statusId = ((Long) m.get("id")).intValue();

            listOrderStatuses.add(statusId);
        }

        for (Object obj : list) {
            profile = new UserProfile();
            vo      = new UserSearchResultVO();
            user    = new OldUser();
            row     = (Map) obj;
            profile.setId(((Long) row.get(F_ID)).intValue());
            profile.setUserId(((Long) row.get("userid")).intValue());
            profile.setFirstName((String) row.get(F_FIRST_NAME));
            profile.setMiddleName((String) row.get(F_MIDDLE_NAME));
            profile.setLastName((String) row.get(F_LAST_NAME));
            profile.setBusinessName((String) row.get(F_BUSINESS_NAME));
            profile.setEfin((String) row.get(F_EFIN));
            user.setId(((Long) row.get("userid")).intValue());
            user.setEmail((String) row.get("useremail"));
            vo.setProfile(profile);
            vo.setUser(user);

            int newTrans        = 0;
            int step1Trans      = 0;
            int step2Trans      = 0;
            int unassignedTrans = 0;
            int totalTrans      = 0;

            try {

                // Query transaction information
                Object[] params = new Object[] { user.getId(),
                                                 transactionStatusDao.get(
                                                     AppSettings.TRANSACTION_STATUS_NEW_ID).getName() };

                newTrans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
                params   = new Object[] { user.getId(),
                                          transactionStatusDao.get(
                                              AppSettings.TRANSACTION_STATUS_APPROVED_ID).getName() };
                step1Trans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
                params     = new Object[] { user.getId(),
                                            transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_ORDERED_ID).getName() };
                step2Trans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
                params     = new Object[] { user.getId(),
                                            transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_LOST_ID).getName() };
                unassignedTrans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);

                // total transactions
                totalTrans = getJdbcTemplate().queryForInt(GET_ALL_TRANSACTIONS, new Object[] { user.getId() });
            } catch (Exception e) {}

            vo.setNewTransactionCount(newTrans);
            vo.setStep1TransactionCount(step1Trans);
            vo.setStep2TransactionCount(step2Trans);
            vo.setUnassignedTransactionCount(unassignedTrans);
            vo.setOtherTransactionCount(totalTrans - (newTrans + step1Trans + step2Trans + unassignedTrans));

            // order statistics
            List<Integer> lstOrderCounts = new ArrayList<Integer>();

            for (int ordStatusId : listOrderStatuses) {
                int ordCount = getJdbcTemplate().queryForInt(GET_ORDERS_BY_STATUS, new Object[] { user.getId(),
                        ordStatusId });

                lstOrderCounts.add(ordCount);
            }

            vo.setOrderCounts(lstOrderCounts);
            listResult.add(vo);
        }

        return listResult;
    }

    public List<UserSearchResultVO> getByRole(int roleId) {
        String query = SEARCH_PROFILE;

        if (roleId == 0) {

            // if roleId = 0 then return all user profiles
            query = query.replace(" and rol.id = ", "");
        } else {

            // filter user profiles by role
            query += roleId;
        }

        System.out.println("Role Id = " + roleId);
        System.out.println("SQL = " + query);

        List                     list       = getJdbcTemplate().queryForList(query);
        List<UserSearchResultVO> listResult = new ArrayList<UserSearchResultVO>();
        Map                      row        = null;
        UserProfile              profile    = null;
        UserSearchResultVO       vo         = null;
        OldUser                  user       = null;

        for (Object obj : list) {
            profile = new UserProfile();
            vo      = new UserSearchResultVO();
            user    = new OldUser();
            row     = (Map) obj;
            profile.setId(((Long) row.get(F_ID)).intValue());
            profile.setUserId(((Long) row.get("userid")).intValue());
            profile.setFirstName((String) row.get(F_FIRST_NAME));
            profile.setMiddleName((String) row.get(F_MIDDLE_NAME));
            profile.setLastName((String) row.get(F_LAST_NAME));
            profile.setBusinessName((String) row.get(F_BUSINESS_NAME));
            profile.setEfin((String) row.get(F_EFIN));
            user.setId(((Long) row.get("userid")).intValue());
            user.setEmail((String) row.get("useremail"));
            vo.setProfile(profile);
            vo.setUser(user);
            listResult.add(vo);
        }

        return listResult;
    }
}



